﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace eLoan.Models
{
    public static class MasterFilesHelper
    {
        public static BorrowerAddEditModel GetBorrowerAddEditModel(DatabaseContext db, int? borrowerID, string branchCode, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var branches = db.Branches;
            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            var agentID = agents.Any() ? agents.FirstOrDefault().ID : 0;

            var model = new BorrowerAddEditModel();

            model.DBCommand = borrowerID.HasValue ? AdministrationHelper.DBCommand.Edit.ToString() : AdministrationHelper.DBCommand.Add.ToString();
            model.SelectedBranchCode = branchCode;
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            model.Branches = AdministrationHelper.GetBranchDropDownList(branches);
            model.BorrowerModel = borrowerID.HasValue ? GetBorrowerModel(db, borrowerID.GetValueOrDefault()) : GetBorrowerModelDefaults(branchCode, agentID);

            bool hasActiveLoans = false;
            var activeLoans = db.Loans.Where(w => w.BorrowerID == borrowerID && w.IsActive == true && w.StatusID < (int)AdministrationHelper.LoanStatus.Closed);

            if (activeLoans.Any())
                hasActiveLoans = true;

            model.BorrwerNameIsEditable = !hasActiveLoans;

            return model;
        }

        private static BorrowerModel GetBorrowerModelDefaults(string branchCode, int agentID)
        {
            var model = new BorrowerModel();
            model.Borrower = new Borrower();
            
            model.Borrower.BorrowerName = string.Empty;
            model.Borrower.BranchCode = branchCode;
            model.Borrower.AgentID = agentID;
            model.Borrower.CreditLimit = 10000;
            model.Borrower.Address = string.Empty;
            model.Borrower.ContactNumbers = string.Empty;
            model.Borrower.EmailAddresses = string.Empty;
            model.Borrower.IsActive = true;
            model.BranchName = AdministrationHelper.GetBranchName(branchCode);
            model.AgentName = AdministrationHelper.GetAgentName(agentID);
            model.OldAgentID = agentID;

            return model;

        }

        private static BorrowerModel GetBorrowerModel(DatabaseContext db, int borrowerID)
        {
            var borrower = db.Borrowers.Where(w => w.ID == borrowerID).SingleOrDefault<Borrower>();

            var model = new BorrowerModel();
            model.Borrower = borrower;
            model.BranchName = AdministrationHelper.GetBranchName(borrower.BranchCode);
            model.AgentName = AdministrationHelper.GetAgentName(borrower.AgentID);
            model.OldAgentID = borrower.AgentID;

            return model;

        }

        public static List<BorrowerItem> GetBorrwerList (string branchCode, string searchText, bool excludeInactive = false, bool excludeWithActiveLoans = false)
        {
            var borrowers = new List<BorrowerItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);
                var paramExcludeInactive = new SqlParameter("@ExcludeInactive", excludeInactive);
                var paramExcludeWithActiveLoans = new SqlParameter("@ExcludeWithActiveLoans", excludeWithActiveLoans);

                borrowers = db.Database
                    .SqlQuery<BorrowerItem>("sp_GetBorrowerList @BranchCode, @SearchText, @ExcludeInactive, @ExcludeWithActiveLoans", 
                                                            paramBranchCode, paramSearchText, paramExcludeInactive, paramExcludeWithActiveLoans)
                    .ToList();
            }

            return borrowers;
        
        }

        public static List<ProspectItem> GetProspectList(string branchCode, int agentID, string userName, string searchText, bool excludeInactive = false)
        {
            var list = new List<ProspectItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramSearchText = new SqlParameter("@SearchText", searchText);
                var paramExcludeInactive = new SqlParameter("@ExcludeInactive", excludeInactive);
                
                list = db.Database
                    .SqlQuery<ProspectItem>("sp_GetProspectList @BranchCode, @AgentID, @SearchText, @ExcludeInactive",
                                                            paramBranchCode, paramAgentID, paramSearchText, paramExcludeInactive)
                    .ToList();
            }

            return list;

        }

        public static BorrowerDetailsModel GetBorrowerDetailsModel(DatabaseContext db, int? borrowerID, string branchCode, string userName)
        {
            var borrowerDetailsModel = new BorrowerDetailsModel();
            
            borrowerDetailsModel.BorrowerName = AdministrationHelper.GetBorrowerName(borrowerID.GetValueOrDefault());
            borrowerDetailsModel.BranchCode = branchCode;
            borrowerDetailsModel.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            borrowerDetailsModel.BorrowerIdentifications = GetBorrowerIdentifications(db, borrowerID);
            borrowerDetailsModel.BorrowerCollaterals = GetBorrowerCollaterals(db, borrowerID);
            borrowerDetailsModel.BorrowerCoMakers = GetBorrowerCoMakers(db, borrowerID);
            borrowerDetailsModel.BorrowerReferences = GetBorrowerReferences(db, borrowerID);

            return borrowerDetailsModel;
        }

        private static List<BorrowerCollateral> GetBorrowerCollaterals(DatabaseContext db, int? borrowerID)
        {
            var collaterals = db.BorrowerCollaterals.Where(w => w.BorrowerID == borrowerID).ToList();

            for (var i = collaterals.Count(); i < 5; i++)
            {
                collaterals.Add(new BorrowerCollateral()
                {
                    BorrowerID = borrowerID.GetValueOrDefault(),
                    Amount = 0,
                    BrandOrFileNo = string.Empty,
                    MakeOrType = string.Empty,
                    SerialOrPlateNo = string.Empty
                });
            }

            return collaterals;
        }

        private static List<BorrowerCoMaker> GetBorrowerCoMakers(DatabaseContext db, int? borrowerID)
        {
            var comakers = db.BorrowerCoMakers.Where(w => w.BorrowerID == borrowerID).ToList();

            for (var i = comakers.Count(); i < 5; i++)
            {
                comakers.Add(new BorrowerCoMaker()
                {
                    BorrowerID = borrowerID.GetValueOrDefault(),
                    Address = string.Empty,
                    CoMakerName = string.Empty,
                    ContactNumbers = string.Empty,
                    IDNo = string.Empty
                });
            }

            return comakers;
        }

        private static List<BorrowerIdentification> GetBorrowerIdentifications(DatabaseContext db, int? borrowerID)
        {
            var ids = db.BorrowerIdentifications.Where(w => w.BorrowerID == borrowerID).ToList();

            for (var i = ids.Count(); i < 5; i++)
            {
                ids.Add(new BorrowerIdentification()
                {
                    BorrowerID = borrowerID.GetValueOrDefault(),
                    ID = 0,
                    IDType = string.Empty,
                    IDNo = string.Empty,
                    Remarks = string.Empty
                });
            }

            return ids;
        }

        private static List<BorrowerReference> GetBorrowerReferences(DatabaseContext db, int? borrowerID)
        {
            var references = db.BorrowerReferences.Where(w => w.BorrowerID == borrowerID).ToList();

            for (var i = references.Count(); i < 7; i++)
            {
                references.Add(new BorrowerReference()
                {
                    BorrowerID = borrowerID.GetValueOrDefault(),
                    ID = 0,
                    Name = string.Empty,
                    Relationship = string.Empty,
                    ContactNumbers = string.Empty
                });
            }

            return references;
        }

        public static BorrowerSavingsModel GetBorrowerSavingsModel(DatabaseContext db, int? borrowerID)
        {
            var model = new BorrowerSavingsModel();
            model.BorrowerID = borrowerID.GetValueOrDefault();
            model.BorrowerName = AdministrationHelper.GetBorrowerName(borrowerID.GetValueOrDefault());
            model.Savings = db.BorrowerSavings.Where(w => w.BorrowerID == borrowerID).ToList();

            decimal totalSavings = 0;

            if (model.Savings.Any())
                totalSavings = model.Savings.Sum(s => s.Amount);

            model.TotalSavings = totalSavings;

            return model;

        }

        public static BorrowerLoanHistoryModel GetBorrowerLoanHistoryModel(DatabaseContext db, int? borrowerID)
        {
            var model = new BorrowerLoanHistoryModel();

            model.BorrowerID = borrowerID.GetValueOrDefault();
            model.BorrowerName = AdministrationHelper.GetBorrowerName(borrowerID.GetValueOrDefault());
            
            var loans = db.Loans.Where(w => w.StatusID == (int)AdministrationHelper.LoanStatus.Closed && w.BorrowerID == borrowerID);

            var loanList = new List<LoanHistoryItem>();
            foreach(var item in loans)
            {
                var loan = new LoanHistoryItem();
                loan.DocumentNo = item.DocumentNo;
                loan.DocumentDate = item.DocumentDate;
                loan.Remarks = ((WorkspaceHelper.CloseLoanRemark)item.RemarkID).ToDescription();
                loan.AgentName = AdministrationHelper.GetAgentName(item.AgentID);
                loan.StatusName = AdministrationHelper.GetLoanStatusName(item.StatusID);
                
                loanList.Add(loan);
            }

            model.LoanList = loanList;

            return model;
        }

        public static ProspectAddEditModel GetProspectAddEditModel(DatabaseContext db, string branchCode, int? id)
        {
            var model = new ProspectAddEditModel();
            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            var agentID = agents.Any() ? agents.FirstOrDefault().ID : 0;

            model.DBCommand = id.HasValue ? AdministrationHelper.DBCommand.Edit.ToString() : AdministrationHelper.DBCommand.Add.ToString();
            model.SelectedBranchCode = branchCode;
            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SelectedAgentID = agentID;
            model.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            model.Prospect = id.HasValue ? 
                                db.Prospects.Where(w => w.ID == id).SingleOrDefault<Prospect>() : 
                                new Prospect()
                                {
                                    BranchCode = branchCode,
                                    DateAdded = DateTime.Today,
                                    IsActive = true
                                };

            return model;
        }
    }
}